package com.bootdo.shop.dao.provider;

import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.bootdo.shop.domain.Category;
import com.bootdo.shop.domain.GoodsLimit;
import com.bootdo.shop.domain.request.GoodsReq;


public class GoodsMapperProvider {
	
	
	public String goodsHomeList(final GoodsReq goodsReq){
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("select id,qr_code,title,model,subject,market_price,sale_price,is_hot,is_recommend,thumbnail_image,");
		sBuffer.append("description,buy_count from goods where is_onsale = 1 and is_home_page = 1 order by sort_order desc");
		if(goodsReq.getLimit() != null && goodsReq.getLimit() !=0){
			sBuffer.append(" limit #{start},#{limit}");
		}
		return sBuffer.toString();
	}
	
	public String notSearchrecommendGoodsList(){
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("select g.id,g.qr_code,g.title,g.model,g.subject,g.market_price,g.sale_price,g.is_hot,g.is_recommend,thumbnail_image,");
		sBuffer.append(" g.description,g.intr_url,g.buy_count from goods g ,t_good_not_search_recommend tg where g.id=tg.recommend_good_id ");
		sBuffer.append(" order by tg.seq desc ");
		return sBuffer.toString();
	}
	
	public String goodsList(final GoodsReq goodsReq){
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("select g.* from goods g ");
		if(goodsReq.getCategoryId() != null && goodsReq.getCategoryId() != 0){
			sBuffer.append(" left join goods_category gc on g.id = gc.goods_id ");
		}
		sBuffer.append(" where 1 = 1 ");
		if(goodsReq.getIsOnsale() != null) {
			sBuffer.append(" and g.is_onsale = #{isOnsale} ");
		}
		if(goodsReq.getCategoryId() != null && goodsReq.getCategoryId() != 0){
			sBuffer.append(" and gc.category_id = #{categoryId} ");
		}
		if(StringUtils.isNotBlank(goodsReq.getKeyword())){
			sBuffer.append(" and ( g.title like concat('%',#{keyword},'%')");
			if(goodsReq.getKeyword().matches("^\\d+$")){
				sBuffer.append("  or g.id=#{keyword} ");
			}
			sBuffer.append(" )");
		}
		if(goodsReq.getSortConfig() != null){
			sBuffer.append(goodsReq.getSortConfig());
		}else{
			sBuffer.append(" order by g.sort_order desc");
		}
		if(goodsReq.getLimit() != null && goodsReq.getLimit() !=0){
			sBuffer.append(" limit #{start},#{limit}");
		}
		return sBuffer.toString();
	}
	
	public String goodsListCount(final GoodsReq goodsReq){
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("select count(1) from goods g ");
		if(goodsReq.getCategoryId() != null && goodsReq.getCategoryId() != 0){
			sBuffer.append(" left join goods_category gc on g.id = gc.goods_id ");
		}
		sBuffer.append(" where 1 = 1 ");
		if(goodsReq.getIsOnsale() != null) {
			sBuffer.append(" and g.is_onsale = #{isOnsale} ");
		}
		if(goodsReq.getCategoryId() != null && goodsReq.getCategoryId() != 0){
			sBuffer.append(" and gc.category_id = #{categoryId} ");
		}
		if(StringUtils.isNotBlank(goodsReq.getKeyword())){
			sBuffer.append(" and ( g.title like concat('%',#{keyword},'%')");
			if(goodsReq.getKeyword().matches("^\\d+$")){
				sBuffer.append("  or g.id=#{keyword} ");
			}
			sBuffer.append(" )");
		}
		return sBuffer.toString();
	}
	
	public String medtronicGoodsList(final GoodsReq goodsReq){
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("select g.id,g.qr_code,g.title,g.model,g.subject,g.market_price,g.sale_price,g.is_hot,g.is_recommend,thumbnail_image,");
		sBuffer.append("g.description,g.intr_url from goods g ");
		if(goodsReq.getCategoryId() != null && goodsReq.getCategoryId() != 0){
			sBuffer.append(" left join goods_category gc on g.id = gc.goods_id ");
		}
		sBuffer.append(" where 1 = 1 ");
		if(goodsReq.getCategoryId() != null && goodsReq.getCategoryId() != 0){
			sBuffer.append(" and gc.category_id = #{categoryId} ");
		}
		if(goodsReq.getSortConfig() != null){
			sBuffer.append(goodsReq.getSortConfig());
		}else{
			sBuffer.append(" order by g.sort_order desc ");	
		}
		sBuffer.append(" limit #{start},#{limit}");
		return sBuffer.toString();
	}
	
	public String marketingActivityGoodsList(final GoodsReq goodsReq){
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("select g.id,g.qr_code,g.title,g.model,g.subject,g.market_price,g.sale_price,g.is_hot,g.is_recommend,thumbnail_image,");
		sBuffer.append("g.description,g.intr_url from goods g ,marketing_activity_goods b where g.id = b.goods_id and b.marketing_activity_id = #{marketingActivityId}");
		sBuffer.append(" and g.is_onsale = 1 ");
		if(goodsReq.getSortConfig() != null){
			sBuffer.append(goodsReq.getSortConfig());
		}else{
			sBuffer.append(" order by b.id asc ");	
		}
		if(goodsReq.getLimit() != null && goodsReq.getLimit() !=0){
			sBuffer.append(" limit #{start},#{limit}");
		}
		return sBuffer.toString();
	}

	
	public static final String GOODS_COMMENT_COLUMN = "id, order_id, goods_id, user_id, is_manager, comment, goods_source, shipping_source, help_source, avg_source, "
			+ "is_open, status, create_time, last_modify_time,problem_solving,response_speed,communicate_attitude,service_tag_id";
	
	
	public String getServiceCommentResByCodeNos(HashMap<String,Object> param){
		ArrayList<String> orderNos = (ArrayList<String>)param.get("list");
		StringBuilder sb = new StringBuilder();
		sb.append("select od.order_no,od.id,sc.`status`,sc.`comment`,sc.is_open from order_info od,shop_comment sc where od.id = sc.order_id  ");
		MessageFormat msf =new MessageFormat("#'{'list[{0}]}");
		if(orderNos.size()>0){
			sb.append("and od.order_no in (" );
			for(int i=0;i< orderNos.size();i++){
				sb.append(msf.format(new Integer[]{i}));
				sb.append(",");
			}
			sb.deleteCharAt(sb.length()-1);
			sb.append(")");
		}
		return sb.toString();
	}
	
	
	public String getGoodsLimitList(GoodsLimit goodsLimit){
		StringBuilder sb = new StringBuilder();
		sb.append("select * from goods_limit where goods_id = #{goodsId}");
		if(goodsLimit.getSpecificationId() != null && goodsLimit.getSpecificationId() > 0){
			sb.append(" and specification_id = #{specificationId}");
		}
		return sb.toString();
	}
	
	public String shoppingCartRecommend(Map<String, Object> map){
		List<Integer> goodsList = (List<Integer>)map.get("goodsList");
		StringBuffer sb = new StringBuffer();
		sb.append("select g.id,g.qr_code,g.title,g.model,g.subject,g.market_price,g.sale_price,g.is_hot,g.is_recommend,thumbnail_image,");
		sb.append("g.intr_url,g.buy_count,(select min(a.sale_price) from t_good_specification a where a.good_id = g.id and a.status = 1)"
				+ " specification_lowest_price from goods g,goods_cart_recommend gcr where g.is_onsale = 1 and g.id = gcr.recommend_id  ");
		MessageFormat msf = new MessageFormat("#'{'goodsList[{0}]}");
		if(goodsList.size()>0){
			sb.append(" and gcr.goods_id in ( " );
			for(int i=0;i< goodsList.size();i++){
				sb.append(msf.format(new Integer[]{i}));
				sb.append(",");
			}
			sb.deleteCharAt(sb.length()-1);
			sb.append(")");
		}else{
			sb.append(" and 1 > 2 ");
		}
		return sb.toString();
	}
	
	public String verifyGoodsIsService(Map<String,Integer> param){
		Integer goodsId = param.get("goodsId");
		Integer specificationId = param.get("specificationId");
		StringBuffer sb = new StringBuffer();
		sb.append("select sum(is_service) serviceSum,count(1) goodsCount "
				+ " from store_goods_item_mapping a ,store_item b where a.item_id = b.id and a.goods_id = #{goodsId} and a.status = 1 ");
		if(null != specificationId && specificationId != 0){
			sb.append(" and a.specification_id = #{specificationId}");
		}else {
			sb.append(" and a.specification_id = 0 ");
		}
		return sb.toString();
	}
	
	public String updateCategory(Category category) {
		StringBuilder sb = new StringBuilder();
		sb.append("update category set title = #{title},sort_order=#{sortOrder}");
		if(category.getStatus() != null) {
			sb.append(",status = #{status} ");
		}
		sb.append(" where id = #{id}");
		return sb.toString();
	}
	
	public String addCategory(Category category) {
		StringBuilder sb = new StringBuilder();
		sb.append("insert into category(title,sort_order  ");
		if(category.getStatus() != null) {
			sb.append(",status ");
		}
		sb.append(") values ( #{title},#{sortOrder} ");
		if(category.getStatus() != null) {
			sb.append(", #{status} ");
		}
		sb.append(" ) ");
		return sb.toString();
	}

	public String updateGoods(GoodsReq goodsReq) {
		StringBuilder sb = new StringBuilder();
		sb.append(" update goods set title = #{title}");
		sb.append(" ,sku = #{sku} ");
		sb.append(" ,subject = #{subject} ");
		if(goodsReq.getThumbnailImage() != null && !goodsReq.getThumbnailImage().equals("")) {
			sb.append(" ,thumbnail_image = #{thumbnailImage} ");
		}
		sb.append(" ,market_price = #{marketPrice} ");
		sb.append(" ,sale_price = #{salePrice} ");
		sb.append(" ,description = #{description} ");
		sb.append(" ,is_onsale = #{isOnsale} ");
		sb.append(" ,sort_order = #{sortOrder} ");
		sb.append(" ,post_free = #{postFree} ");
		sb.append(" ,amount = #{amount} ");
		sb.append(",general_user_price = #{generalUserPrice}");
		sb.append(",platina_user_price = #{platinaUserPrice}");
		sb.append(",vip_user_price = #{vipUserPrice}");
		sb.append(",agent_1_price = #{agent1Price}");
		sb.append(",agent_2_price = #{agent2Price}");
		sb.append(",agent_3_price = #{agent3Price}");
		sb.append(",agent_4_price = #{agent4Price}");
		sb.append(",agent_5_price = #{agent5Price}");
		sb.append(",is_fr = #{isFr}");
		if(goodsReq.getGeneralUserCredits() == null) {
			sb.append(" ,general_user_credits = 0 ");
		}else {
			sb.append(" ,general_user_credits = #{generalUserCredits} ");
		}
		if(goodsReq.getPlatinaUserCredits() == null) {
			sb.append(" ,platina_user_credits = 0 ");
		}else {
			sb.append(" ,platina_user_credits = #{platinaUserCredits} ");
		}
		if(goodsReq.getGeneralUserCredits() == null) {
			sb.append(" ,vip_user_credits = 0 ");
		}else {
			sb.append(" ,vip_user_credits = #{vipUserCredits} ");
		}
		if(goodsReq.getAgent1Credits() == null) {
			sb.append(" ,agent_1_credits = 0 ");
		}else {
			sb.append(" ,agent_1_credits = #{agent1Credits} ");
		}
		if(goodsReq.getAgent2Credits() == null) {
			sb.append(" ,agent_2_credits = 0 ");
		}else {
			sb.append(" ,agent_2_credits = #{agent2Credits} ");
		}
		if(goodsReq.getAgent3Credits() == null) {
			sb.append(" ,agent_3_credits = 0 ");
		}else {
			sb.append(" ,agent_3_credits = #{agent3Credits} ");
		}
		if(goodsReq.getAgent4Credits() == null) {
			sb.append(" ,agent_4_credits = 0 ");
		}else {
			sb.append(" ,agent_4_credits = #{agent4Credits} ");
		}
		if(goodsReq.getAgent5Credits() == null) {
			sb.append(" ,agent_5_credits = 0 ");
		}else {
			sb.append(" ,agent_5_credits = #{agent5Credits} ");
		}
		sb.append(" where id  = #{id} ");
		return sb.toString();
	}
	
	public String addGoods(GoodsReq goodsReq) {
		StringBuilder sb = new StringBuilder();
		sb.append("insert into goods (title,sku,subject,thumbnail_image,market_price,sale_price,description,is_onsale,amount");
		if(goodsReq.getSortOrder() != null) {
			sb.append(",sort_order");
		}
		sb.append(",post_free,general_user_credits,platina_user_credits,vip_user_credits,agent_1_credits,agent_2_credits,agent_3_credits,agent_4_credits,agent_5_credits");
		sb.append(",general_user_price,platina_user_price,vip_user_price,agent_1_price,agent_2_price,agent_3_price,agent_4_price,agent_5_price,is_fr) ");
		sb.append(" values (#{title},#{sku},#{subject},#{thumbnailImage},#{marketPrice},#{salePrice},#{description},#{isOnsale},#{amount}");
		if(goodsReq.getSortOrder() != null) {
			sb.append(",#{sortOrder}");
		}		
		sb.append(",#{postFree},#{generalUserCredits},#{platinaUserCredits},#{vipUserCredits},#{agent1Credits},#{agent2Credits},#{agent3Credits},#{agent4Credits},#{agent5Credits}");
		sb.append(",#{generalUserPrice},#{platinaUserPrice},#{vipUserPrice},#{agent1Price},#{agent2Price},#{agent3Price},#{agent4Price},#{agent5Price},#{isFr})");
		return sb.toString();
	}
	
	
}